package dbops

import (
	"database/sql"
	"github.com/satori/go.uuid"
	"log"
	"time"
	"video_server/api/defs"
)

func AddUserCredential(loginName string ,pwd string) error {
	 stmIns,err := dbConn.Prepare("INSERT INTO users(login_name,pwd) VALUES(?,?)")
	if err != nil {
		return err
	}
	_, _ = stmIns.Exec(loginName, pwd)
	_ = stmIns.Close()
	 return nil

}

func GetUserCredential(loginName string)(string, error)  {
	stmOut,err := dbConn.Prepare("SELECT pwd FROM users WHERE login_name = ?")
	if err != nil {
		log.Printf("%s",err)
		return "",err
	}
	var pwd string
	_ = stmOut.QueryRow(loginName).Scan(&pwd)
	_ = stmOut.Close()
	return pwd,nil
}

func GetUser(loginName string) (*defs.UserCredential,error)  {
	stmtOut,err := dbConn.Prepare("SELECT * FROM users WHERE login_name = ?")
	var userId int
	var userName string
	var pwd string
	if err != nil{
		log.Printf("Get user infomations error :%s",err)
		return nil,err
	}
	err = stmtOut.QueryRow(loginName).Scan(&userId, &userName, &pwd)
	if err != nil && err != sql.ErrNoRows{
		return nil,err
	}
	if err == sql.ErrNoRows {
		return nil,nil
	}
	defer stmtOut.Close()
	res := &defs.UserCredential{Id:userId,Username:userName,Pwd:pwd}
	return res,nil
}


func DeleteUser(loginName string,pwd string) error{
	stmDel,err := dbConn.Prepare("DELETE FROM users WHERE login_name = ? and  pwd = ?")
	if err != nil {
		log.Printf("Delete user error : %s",err)
		return err
	}
	_, _ = stmDel.Exec(loginName, pwd)
	_ = stmDel.Close()
	return nil

}


func AddNewVideo(aid int,name string)(*defs.VideoInfo,error){
	vid, err := uuid.NewV4()
	if err != nil{
		return nil,err
	}
	t := time.Now()
	ctime := t.Format("Jan 02 2006, 15:04:05") //m d y , HH:MM:SS

	stmIns,err := dbConn.Prepare(`INSERT INTO video_info(id,author_id,name,display_ctime) VALUES (?,?,?,?)`)
	if err != nil {
		return nil,err	
	}
	_, err = stmIns.Exec(vid.String(), aid, name, ctime)
	if err != nil {
		return nil,err
	}

	res := &defs.VideoInfo{Id:vid.String(),AuthorId:aid,Name:name,DisplayCtime:ctime}

	defer stmIns.Close()
	return res,nil

}

func GetVideoInfo(vid string)(*defs.VideoInfo,error){
	stmOut,err :=dbConn.Prepare("SELECT author_id,name,display_ctime FROM video_info WHERE id = ?")
	var aid int
	var dct string
	var name string
	err = stmOut.QueryRow(vid).Scan(&aid, &name, &dct)
	if err != nil  && err != sql.ErrNoRows{
		return nil,err
	}
	if err == sql.ErrNoRows {
		return nil,nil
	}
	defer stmOut.Close()

	res := &defs.VideoInfo{Id:vid,AuthorId:aid,Name:name,DisplayCtime:dct}
	return res,nil
}

func DeleteVideoInfo(vid string)error{
	stmDel, err := dbConn.Prepare("DELETE FROM video_info WHERE id = ?")
	if err != nil {
		return nil
	}
	_, err = stmDel.Exec(vid)
	if err != nil {
		return err
	}
	defer stmDel.Close()
	return nil
}

func AddNewComments(vid string,aid int,content string) error  {
	id,err := uuid.NewV4()
	if err != nil {
		return err
	}

	stmtIns,err := dbConn.Prepare("INSERT INTO comments (id,video_id,author_id,content)VALUES(?,?,?,?)")
	if err != nil {
		return err
	}
	_,err = stmtIns.Exec(id.String(),vid,aid,content)
	if err != nil {
		return err
	}
	defer stmtIns.Close()
	return nil
}

func ListConments(vid string,from,to int)([]*defs.Comment,error)  {
	stmtOut,err := dbConn.Prepare(`SELECT comments.id,users.login_name,comments.content FROM comments 
INNER JOIN users ON comments.author_id = users.id 
WHERE comments.video_id = ? AND comments.time > FROM_UNIXTIME(?) AND comments.time <= FROM_UNIXTIME(?)
ORDER BY comments.time DESC `)
	var res []*defs.Comment
	rows ,err := stmtOut.Query(vid,from,to)
	if err != nil {
		return res,err
	}
	for rows.Next(){
		var id,name,content string
		if err := rows.Scan(&id,&name,&content);err != nil{
			return res,err
		}
		c:=&defs.Comment{Id:id,VideoId:vid,Author:name,Content:content}
		res = append(res, c)
	}
	defer stmtOut.Close()
	return res,nil
}

func ListVideoInfo(userName string,from,to int)([]*defs.VideoInfo,error)  {
	stmtOut,_ := dbConn.Prepare(`SELECT v.id,v.author_id,v.name,v.display_ctime 
FROM video_info v LEFT JOIN users u ON u.id = v.author_id WHERE u.login_name = ? 
ORDER BY v.display_ctime DESC`)
	var res []*defs.VideoInfo
	rows,_ := stmtOut.Query(userName,from,to)
	if err != nil{
		return res,err
	}
	for rows.Next(){
		var id,vname,dctime string
		var userId int
		if err := rows.Scan(&id,&userId,&vname,&dctime);err != nil{
			return res,nil
		}
		v := &defs.VideoInfo{Id:id,AuthorId:userId,Name:vname,DisplayCtime:dctime}
		res = append(res,v)
	}
	defer stmtOut.Close()
	return res,nil
}




